package com.campus.dao.provider;

import com.campus.config.SysUserConfig;
import com.campus.pojo.SysUser;
import com.campus.tools.CommonValue;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

public class UserDynaSqlProvider {
    public String selectByNameAndPwd(Map<String,Object> params){
        String name = (String) params.get("name");
        String password = (String) params.get("psw");
        /*String sql = new SQL(){
            {
                SELECT("*");
                FROM("")
            }
        }.toString();*/

        String sql = "select " + " su.*," + " (case when su.userType=1 then '院系账号'"
                + "  when su.userType=3 then '平台管理员'" + "  when su.userType=4 then '院校管理员'"
                + "  else pui.name end)  as unitName," + "  sa_weiLeaf.type as areaType," + "  (case "
                + "  when sa_weiLeaf.type=3 then CONCAT(sa_province.name,'.',sa_city.name,'.',sa_weiLeaf.name)"
                + "  when sa_weiLeaf.type=2 then CONCAT(sa_city.name,'.',sa_weiLeaf.name)"
                + "  when sa_weiLeaf.type=1 then CONCAT(sa_weiLeaf.name)" + "  else '未知' end" + "  ) as areaName"
                + "   from sys_user as su " + " left join pro_unitinfo as pui" + "  on su.unitId=pui.id"
                + "  left join sys_area as sa_weiLeaf" + "  on su.areaId=sa_weiLeaf.areaNumber"
                + "  left join sys_area as sa_city" + "  on sa_weiLeaf.parentId=sa_city.areaNumber"
                + "  left join sys_area as sa_province" + "  on sa_city.parentId=sa_province.areaNumber"
                + "  where 1=1 ";
        sql += " and su.delState=1";
        sql += " and su.name='" + name + "' and su.psw='" + password + "'";
        //System.out.println(sql);//调试
        return sql;
    }

    public String count(Map<String,Object> params){     //查询合法记录总数
        String sql = new SQL(){
            {
                SELECT("count(*)");
                FROM("sys_user as s");
                WHERE("s.delState = 1");
                if (params.get("user") != null){
                    SysUser user = (SysUser) params.get("user");
                    if (user.getName() != null && !user.getName().equals("")){
                        WHERE("s.name like CONCAT('%',#{user.name},'%')");
                    }
                    if(user.getAreaId() != null && !user.getAreaId().equals("")){
                        WHERE("s.areaId in (select areaNumber from sys_area as a where a.areaNumber= #{user.areaId} " +
                                                     "union all select areaNumber from sys_area as sa where sa.parentId= #{user.areaId}  " +
                                                     "union all select areaNumber from sys_area as sa where sa.parentId in (select areaNumber from sys_area as sa where sa.parentId= #{user.areaId}))");
                    }
                    WHERE("s.userType = #{user.userType}");
                    ORDER_BY("operatorTime desc");
                }
            }
        }.toString();
        //System.out.println(sql+'\n'+"----------------------------------");//调试
        return  sql;
    }

    public String selectAreaUserByPage(Map<String,Object> params){   //分页查询区域用户
        String sql = SysUserConfig.getAllAreaAdmin;
        if (params.get("user") != null) {
            SysUser user = (SysUser) params.get("user");
            //System.out.println(user);//调试
            if (user.getName() != null && !"".equals(user.getName())) {
                sql += " and su.name LIKE '%" + user.getName() + "%' ";
            }
            if (user.getAreaId() != null && !user.getAreaId().isEmpty()) {
                sql += " and su.areaId in (select areaNumber from sys_area as sa where sa.areaNumber= '"
                        + user.getAreaId() + "'";//areaId是校的情况
                sql += " union all select areaNumber from sys_area as sa where sa.areaNumber in (select areaNumber from sys_area as sa where sa.parentId='"
                        + user.getAreaId() + "')";//areaId是市的情况
                sql += " union all select c.areaNumber from sys_area as c where c.areaNumber in (select sa.areaNumber from sys_area as sa where sa.parentId in (select sa.areaNumber from sys_area as sa where sa.parentId = '"
                        + user.getAreaId() + "')))";//areaId是省的情况
            }
            sql += " and su.userType=" + user.getUserType() + "";
            sql += " order by operatorTime desc";
        }

        if (params.get("pager") != null) {
            sql += " limit #{pager.firstLimitParam} , #{pager.perPageRows}";
        }
        //System.out.println(sql+'\n'+"----------------------------------");//调试
        return sql;
    }

    // 动态查询用户总记录数
    public String count1(Map<String, Object> params) {
        String sql = SysUserConfig.getAllUser;
        if (params.get("user") != null) {
            SysUser user = (SysUser) params.get("user");
            if (user.getName() != null && !user.getName().isEmpty()) {
                sql += " and su.name like '%" + user.getName() + "%'";
            }
            if (user.getUnitId() != null && !user.getUnitId().isEmpty()) {
                sql += " and su.unitId like '%" + user.getUnitId() + "%'";
            }
            if (user.getUserType() != null && !user.getUserType().isEmpty()) {
                sql += " and su.userType like '%" + user.getUserType() + "%'";
            }
            if (user.getAreaId() != null && !user.getAreaId().isEmpty()) {
                sql += " and su.areaId like '%" + user.getAreaId() + "%'";
            }
            sql += " and su.userType in ('" + CommonValue.UserType_Shebao + "','" + CommonValue.UserType_Unit + "')";
            sql += " group by su.userCode order by operatorTime desc";
        }
        return sql;
    }

    // 分页动态查询用户
    public String selectWithParam1(Map<String, Object> params) {
        String sql = SysUserConfig.getAllUser;
        if (params.get("user") != null) {
            SysUser user = (SysUser) params.get("user");
            if (user.getName() != null && !user.getName().isEmpty()) {
                sql += " and su.name like '%" + user.getName() + "%'";
            }
            if (user.getUnitId() != null && !user.getUnitId().isEmpty()) {
                sql += " and su.unitId like '%" + user.getUnitId() + "%'";
            }
            if (user.getUserType() != null && !user.getUserType().isEmpty()) {
                sql += " and su.userType like '%" + user.getUserType() + "%'";
            }
            if (user.getAreaId() != null && !user.getAreaId().isEmpty()) {
                sql += " and su.areaId like '%" + user.getAreaId() + "%'";
            }
            sql += " and su.userType in ('" + CommonValue.UserType_Shebao + "','" + CommonValue.UserType_Unit + "')";
            sql += " group by su.userCode order by operatorTime desc";
        }

        if (params.get("pager") != null) {
            sql += " limit #{pager.firstLimitParam} , #{pager.perPageRows}  ";
        }
        //System.out.println(sql);//调试
        return sql;
    }
}
